#!/usr/bin/perl
#Creates files for creating of DB Schema

use Config::INI::Reader;
my $cfg=Config::INI::Reader->read_file('../etc/5005db.ini');

$dbtablespace= $cfg->{_}->{dbsid};
$dbpw= $cfg->{_}->{dbsid};
$dbuser = 



create tablespace $tablespace 
datafile '/opt/oradata/rssdb/rsstblsp/rss01.dbf'
size 2000M
autoextend on 
next 1000M 
extent management local;


create table rssDownload 
(rssD_id number primary key, rssD_filename varchar(50), rssD_hashcode varchar(35));
create table rssFeeds
(rssFeedid number(6), rssFeedURL varchar2(200),rssFeedStatus char(1) )
tablespace rsstblsp;
create table rssFile
(rssF_id number(6), rssD_id number(6),rssF_filename varchar2(60), exported varchar2(5), rssF_clob clob)
tablespace rsstblsp;

create tablespace rsstblsp 
datafile '/opt/oradata/rssdb/rsstblsp/rss01.dbf'
size 2000M
autoextend on 
next 1000M 
extent management local;
drop sequence rssQD;
create sequence rssQD 
	minvalue 1
	maxvalue 99999999999
	start with 2
	increment by 1
	cache 20;
drop sequence rssfeedseq;
create sequence rssfeedseq
	minvalue 1
	maxvalue 99999999999
	start with 1
	increment by 1;
drop sequence rssQF;
create sequence rssQF
	minvalue 1
	maxvalue 99999999999
	start with 1
	increment by 1
	cache 20;
create index 
	MD5_Indx
on
	rssDownload (RSSD_HASHCODE)
tablespace
	rsstblsp;	
create or replace procedure rssClob_Download(rssid in number ,fName in varchar2)
as
        l_blob  clob;
        l_bfile bfile;

begin
        insert into rssFile (rssF_id,rssD_id,rssF_clob) VALUES (rssQF.NEXTVAL,rssid, empty_clob() )
        returning rssF_Clob into l_blob;

        l_bfile := bfilename( 'DOWNLOAD',fName );
        dbms_lob.fileopen( l_bfile);

        dbms_lob.loadfromfile( l_blob, l_bfile, dbms_lob.getlength( l_bfile));
        dbms_lob.fileclose( l_bfile );
end;
/

CREATE OR REPLACE PROCEDURE extractRssFile(INrssF_id in number, FileName in varchar2) IS

vblob CLOB;


BEGIN
SELECT rssF_clob
INTO vblob
FROM rssFile
WHERE rssF_id = INrssF_id;

dbms_xslprocessor.clob2file(vblob, 'LOCAL',FileName);

END;
/
